from typing import Optional, List, Union
from sqlalchemy import desc, asc
from sqlalchemy.orm import Query, Session, joinedload

from app.core.pagination import Pageable

class SqlUtils:
    @staticmethod
    def query(
        db: Session, 
        model, 
        *filters, 
        order_by: Optional[Union[str, List[Union[str, object]]]] = None,
        pageable: Optional[Pageable] = None,
        limit: Optional[int] = None,
        relationships: Optional[List[str]] = None
    ) -> Query:
        """支持多种排序格式的查询方法"""
        query = db.query(model)
        
        # 处理关系加载
        if relationships:
            for rel in relationships:
                query = query.options(joinedload(getattr(model, rel)))
        
        # 处理过滤条件
        if filters:
            query = query.filter(*filters)
        
        # 处理排序
        if order_by:
            order_clauses = SqlUtils.build_order_clauses(model, order_by)
            query = query.order_by(*order_clauses)
        
        if pageable:
            skip, limit = pageable.to_skip_limit()
            query = query.offset(skip).limit(limit)
        
        if limit:
            query = query.limit(limit)
        
        return query

    @staticmethod
    def build_order_clauses(model, order_by):
        """构建排序子句，支持多种格式"""
        order_clauses = []
        
        if isinstance(order_by, (list, tuple)):
            # 列表格式
            for item in order_by:
                if isinstance(item, str):
                    # 字符串："create_at" 或 "create_at desc"
                    if ' ' in item:
                        field, direction = item.rsplit(' ', 1)
                        column = getattr(model, field.strip())
                        order_clauses.append(column.desc() if direction.lower() == 'desc' else column)
                    else:
                        order_clauses.append(getattr(model, item))
                else:
                    # 已经是排序表达式，如 User.create_at.desc()
                    order_clauses.append(item)
        
        elif isinstance(order_by, str):
            # 单个字符串
            if ' ' in order_by:
                field, direction = order_by.rsplit(' ', 1)
                column = getattr(model, field.strip())
                order_clauses.append(column.desc() if direction.lower() == 'desc' else column)
            else:
                order_clauses.append(getattr(model, order_by))
        
        else:
            # 单个排序表达式
            order_clauses.append(order_by)
        
        return order_clauses